<?php
/**
 * Created by PhpStorm.
 * User: lsk
 * Date: 17/2/8
 * Time: 10:18
 */

namespace common\helpers;

class PHPExcel
{

    const ROW_HEIGHT = 18;
    const COLUMN_WIDTH = 30;

    public static $excelMimeTypes = [
        'application/vnd.ms-office',
        'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        'application/vnd.ms-excel',
        'application/octet-stream',
    ];

    public static function loadExamExcel($excel)
    {
        $old_error_reporting = error_reporting(0);
        $objPHPExcel = \PHPExcel_IOFactory::load($excel);
        error_reporting($old_error_reporting);
        return $objPHPExcel;
    }

    public static function listSheetNames($excel)
    {
        $PHPReader = new \PHPExcel_Reader_Excel2007();
        if(!$PHPReader->canRead($excel))
        {
            $PHPReader = new \PHPExcel_Reader_Excel5();
            if(!$PHPReader->canRead($excel))
            {
                throw new \Exception('未发现Excel文件!');
            }
        }
        $sheetNames  = $PHPReader->listWorksheetNames($excel);
        return $sheetNames;
    }

    public static function sheetToArray($objPHPExcel, $sheetIndex = 0)
    {
        $objPHPExcel->getSheet($sheetIndex);
        return $objPHPExcel->getActiveSheet()->toArray(null, false, false, true);
    }

    public static function validateExcel($tableHeaderData){

      return $tableHeaderData;
    }
    public static function createTemplate()
    {
        $activeSheetIndex = 0;
        $sheetTitle = '导入设备信息模板';
        // Create new PHPExcel object
        $objPHPExcel = self::getPHPExcelObj('class');

        $activeSheet = $objPHPExcel->setActiveSheetIndex($activeSheetIndex);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:I1');
        $activeSheet->setCellValue('A1','注意：前两列不可删除，也不能修改内容和顺序数据从第三列开始填充');
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setARGB('#FF0000');

        // Add some data
        $activeSheet->setCellValue('A2', '线路名称');
        $activeSheet->setCellValue('B2', '身份证号');
        $activeSheet->setCellValue('C2', '考号（非必填）');
        $activeSheet->setCellValue('D2', '语文-150');
        $activeSheet->setCellValue('E2', '数学-150');
        $activeSheet->setCellValue('F2', '英语-150');

        // Rename worksheet
        $objPHPExcel->getActiveSheet()->setTitle($sheetTitle);
        // set row height
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(self::ROW_HEIGHT);
        $objPHPExcel->getActiveSheet()->getColumnDimension()->setWidth(self::COLUMN_WIDTH);
        $objPHPExcel->getActiveSheet()->getColumnDimension()->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getStyle('B')->getNumberFormat()
            ->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
        $objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('A3' )->getText()->createTextRun('PHPExcel:')
        ;  //添加批注
        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);

        $fileName = "设备信息导入模板";
        // set Http headers
        self::setHeaders($fileName);
        // output to a client’s web browser
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    public static function createClassExamTemplate()
    {
        $activeSheetIndex = 0;
        $sheetTitle = '班级成绩通用模板';
        // Create new PHPExcel object
        $objPHPExcel = self::getPHPExcelObj('class');

        $activeSheet = $objPHPExcel->setActiveSheetIndex($activeSheetIndex);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:I1');
        $activeSheet->setCellValue('A1','注意：姓名和身份证号(文本类型)不可删除，科目名称请按照如下"科目名-满分分数值"此格式填写(此行备注内容不可删除）');
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setARGB('#FF0000');

        // Add some data
        $activeSheet->setCellValue('A2', '姓名');
        $activeSheet->setCellValue('B2', '身份证号');
        $activeSheet->setCellValue('C2', '考号（非必填）');
        $activeSheet->setCellValue('D2', '语文-150');
        $activeSheet->setCellValue('E2', '数学-150');
        $activeSheet->setCellValue('F2', '英语-150');

        // Rename worksheet
        $objPHPExcel->getActiveSheet()->setTitle($sheetTitle);
        // set row height
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(self::ROW_HEIGHT);
        $objPHPExcel->getActiveSheet()->getColumnDimension()->setWidth(self::COLUMN_WIDTH);
        $objPHPExcel->getActiveSheet()->getColumnDimension()->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getStyle('B')->getNumberFormat()
            ->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
        $objCommentRichText = $objPHPExcel->getActiveSheet()->getComment('A3' )->getText()->createTextRun('PHPExcel:')
        ;  //添加批注
        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);

        $fileName = "班级成绩模板";
        // set Http headers
        self::setHeaders($fileName);
        // output to a client’s web browser
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    public static function createCourseStudyTemplate(){
        $activeSheetIndex = 0;
        $sheetTitle = '课程学习情况记录模板';
        // Create new PHPExcel object
        $objPHPExcel = self::getPHPExcelObj('class');

        $activeSheet = $objPHPExcel->setActiveSheetIndex($activeSheetIndex);
        // Add some data
        $activeSheet->setCellValue('A1', '姓名');
        $activeSheet->setCellValue('B1', '身份证号');
        $activeSheet->setCellValue('C1', '日常作业');
        $activeSheet->setCellValue('D1', '测试');
        $activeSheet->setCellValue('E1', '其他情况');
        $activeSheet->setCellValue('F1', '其中考试');
        $activeSheet->setCellValue('G1', '期末考试');
        $activeSheet->setCellValue('H1', '学期总评');
        // Rename worksheet
        $objPHPExcel->getActiveSheet()->setTitle($sheetTitle);
        // set row height
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(self::ROW_HEIGHT);
        $objPHPExcel->getActiveSheet()->getColumnDimension()->setWidth(self::COLUMN_WIDTH);
        $objPHPExcel->getActiveSheet()->getColumnDimension()->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getStyle('B')->getNumberFormat()
            ->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);

        $fileName = "课程学习情况记录表固定模板";
        // set Http headers
        self::setHeaders($fileName);
        // output to a client’s web browser
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    public static  function createExperimentalOperationTemplate(){
        $activeSheetIndex = 0;
        $sheetTitle = '实验操作评价固定模板';
        // Create new PHPExcel object
        $objPHPExcel = self::getPHPExcelObj('experimental');

        $activeSheet = $objPHPExcel->setActiveSheetIndex($activeSheetIndex);

        // Add some data
        $activeSheet->setCellValue('A1', '姓名');
        $activeSheet->setCellValue('B1', '身份证号');
        $activeSheet->setCellValue('C1', '次数');
        $activeSheet->setCellValue('D1', '物理实验操作');
        $activeSheet->setCellValue('E1', '化学实验操作');
        $activeSheet->setCellValue('F1', '生物实验操作');
        $activeSheet->setCellValue('G1', '信息技术');
        $activeSheet->setCellValue('H1', '体质健康测试');

        // Rename worksheet
        $objPHPExcel->getActiveSheet()->setTitle($sheetTitle);
        // set row height
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(self::ROW_HEIGHT);
        $objPHPExcel->getActiveSheet()->getColumnDimension()->setWidth(self::COLUMN_WIDTH);
        $objPHPExcel->getActiveSheet()->getColumnDimension()->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getStyle('B')->getNumberFormat()
            ->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);

        $fileName = "实验操作评价表固定模板";
        // set Http headers
        self::setHeaders($fileName);
        // output to a client’s web browser
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    public static function createCourseScoreTemplate(){
        $activeSheetIndex = 0;
        $sheetTitle = '学业成绩及学科课程学习情况汇总表固定模板';
        // Create new PHPExcel object
        $objPHPExcel = self::getPHPExcelObj('course_score');
        $activeSheet = $objPHPExcel->setActiveSheetIndex($activeSheetIndex);
        // Add some data
        $activeSheet->setCellValue('A1', '姓名');
        $activeSheet->setCellValue('B1', '身份证号');
        $activeSheet->setCellValue('C1', '科目名');
        $activeSheet->setCellValue('D1', '考试成绩总积分');
        $activeSheet->setCellValue('E1', '考试成绩平均积分');
        $activeSheet->setCellValue('F1', '考试成绩学期等级');
        $activeSheet->setCellValue('G1', '学习表现总积分');
        $activeSheet->setCellValue('H1', '学习表现平均积分');
        $activeSheet->setCellValue('I1', '学习表现学期等级');
        $activeSheet->setCellValue('J1', '学习能力总积分');
        $activeSheet->setCellValue('K1', '学习能力平均积分');
        $activeSheet->setCellValue('L1', '学习能力学期等级');
        $activeSheet->setCellValue('M1', '学科活动总积分');
        $activeSheet->setCellValue('N1', '学科活动平均积分');
        $activeSheet->setCellValue('O1', '学科活动学期等级');
        $activeSheet->setCellValue('P1', '日常作业或其他总积分');
        $activeSheet->setCellValue('Q1', '日常作业或其他平均积分');
        $activeSheet->setCellValue('R1', '日常作业或其他学期等级');
        // Rename worksheet
        $objPHPExcel->getActiveSheet()->setTitle($sheetTitle);
        // set row height
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(self::ROW_HEIGHT);
        $objPHPExcel->getActiveSheet()->getColumnDimension()->setWidth(self::COLUMN_WIDTH);
        $objPHPExcel->getActiveSheet()->getColumnDimension()->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getStyle('B')->getNumberFormat()
            ->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);

        $fileName = "学业成绩及学科课程学习情况汇总表固定模板";
        // set Http headers
        self::setHeaders($fileName);
        // output to a client’s web browser
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    public static function createAllEvaluateTemplate(){
        $activeSheetIndex = 0;
        $sheetTitle = '学生综合素养评定汇总表固定模板';
        // Create new PHPExcel object
        $objPHPExcel = self::getPHPExcelObj('all-evaluate');

        $activeSheet = $objPHPExcel->setActiveSheetIndex($activeSheetIndex);

        // Add some data
        $activeSheet->setCellValue('A1', '姓名');
        $activeSheet->setCellValue('B1', '身份证号');
        $activeSheet->setCellValue('C1', '评价类型');
        $activeSheet->setCellValue('C2', '自评');
        $activeSheet->setCellValue('C3', '同学评');
        $activeSheet->setCellValue('C4', '教师评');
        $activeSheet->setCellValue('C5', '家长评');
        $activeSheet->setCellValue('C6', '总评');
        $activeSheet->setCellValue('D1', '品德发展水平等第');
        $activeSheet->setCellValue('E1', '品德发展水平得分');
        $activeSheet->setCellValue('F1', '学业发展水平等第');
        $activeSheet->setCellValue('G1', '学业发展水平得分');
        $activeSheet->setCellValue('H1', '身心发展水平等第');
        $activeSheet->setCellValue('I1', '身心发展水平得分');
        $activeSheet->setCellValue('J1', '兴趣与特长等第');
        $activeSheet->setCellValue('K1', '兴趣与特长得分');
        $activeSheet->setCellValue('L1', '社会实践活动等第');
        $activeSheet->setCellValue('M1', '社会实践活动得分');
        $activeSheet->setCellValue('N1', '实验操作等第');
        $activeSheet->setCellValue('O1', '实验操作得分');

        // Rename worksheet
        $objPHPExcel->getActiveSheet()->setTitle($sheetTitle);
        // set row height
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(self::ROW_HEIGHT);
        $objPHPExcel->getActiveSheet()->getColumnDimension()->setWidth(self::COLUMN_WIDTH);
        $objPHPExcel->getActiveSheet()->getColumnDimension()->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getStyle('B')->getNumberFormat()
            ->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);

        $fileName = "学生综合素养评定汇总表固定模板";
        // set Http headers
        self::setHeaders($fileName);
        // output to a client’s web browser
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    public static function createVaccinationTemplate(){
        $activeSheetIndex = 0;
        $sheetTitle = '幼儿疫苗接种记录表固定模板';
        // Create new PHPExcel object
        $objPHPExcel = self::getPHPExcelObj('vaccination');
        $activeSheet = $objPHPExcel->setActiveSheetIndex($activeSheetIndex);

        // Add some data
        $activeSheet->setCellValue('A1', '姓名');
        $activeSheet->setCellValue('B1', '身份证号');
        $activeSheet->setCellValue('C1', '接种疫苗名称');
        $activeSheet->setCellValue('D1', '接种地点');
        $activeSheet->setCellValue('E1', '接种时间');
        $activeSheet->setCellValue('F1', '备注');

        // Rename worksheet
        $objPHPExcel->getActiveSheet()->setTitle($sheetTitle);
        // set row height
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(self::ROW_HEIGHT);
        $objPHPExcel->getActiveSheet()->getColumnDimension()->setWidth(self::COLUMN_WIDTH);
        $objPHPExcel->getActiveSheet()->getColumnDimension()->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(40);
        $objPHPExcel->getActiveSheet()->getStyle('B')->getNumberFormat()
            ->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);

        $fileName = "幼儿疫苗接种记录表固定模板";
        // set Http headers
        self::setHeaders($fileName);
        // output to a client’s web browser
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    public static function createPhysicalCheckTemplate(){
        $activeSheetIndex = 0;
        $sheetTitle = '学生体质检查登记表固定模板';
        // Create new PHPExcel object
        $objPHPExcel = self::getPHPExcelObj('physical_check');
        $activeSheet = $objPHPExcel->setActiveSheetIndex($activeSheetIndex);

        // Add some data
        $activeSheet->setCellValue('A1', '姓名');
        $activeSheet->setCellValue('B1', '身份证号');
        $activeSheet->setCellValue('C1', '身高(cm)');
        $activeSheet->setCellValue('D1', '体重(kg)');
        $activeSheet->setCellValue('E1', '视力(左眼/右眼)');
        $activeSheet->setCellValue('F1', '体重指数(BMI)(kg/m)');
        $activeSheet->setCellValue('G1', '肺活量(ml)');
        $activeSheet->setCellValue('H1', '50米跑(s)');
        $activeSheet->setCellValue('I1', '坐位体前屈(cm)');
        $activeSheet->setCellValue('J1', '立定跳远(cm)');
        $activeSheet->setCellValue('K1', '引力向上(男)／1分钟仰卧起坐(女)(次)');
        $activeSheet->setCellValue('L1', '1000米跑(男)/800米跑(女)(分·秒)');
        $activeSheet->setCellValue('M1', '标准分');
        $activeSheet->setCellValue('N1', '学年总分');
        $activeSheet->setCellValue('O1', '等级评定');

        // Rename worksheet
        $objPHPExcel->getActiveSheet()->setTitle($sheetTitle);
        // set row height
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(self::ROW_HEIGHT);
        $objPHPExcel->getActiveSheet()->getColumnDimension()->setWidth(self::COLUMN_WIDTH);
        $objPHPExcel->getActiveSheet()->getColumnDimension()->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getStyle('B')->getNumberFormat()
            ->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);

        $fileName = "学生体质检查登记表固定模板";
        // set Http headers
        self::setHeaders($fileName);
        // output to a client’s web browser
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }
    public static function createGradeExamTemplate(array $disciplines, array $grade_students, $grade_full_name)
    {
        $activeSheetIndex = 0;
        array_unshift($disciplines, '姓名');
        // 创建PHPExcel object
        $objPHPExcel = self::getPHPExcelObj('grade');

        // 循环年级，每个班级为一个sheet
        foreach ($grade_students as $per_class) {
            $current_class_student_names = array_column($per_class['students'],'name');
            $sheetTitle = $per_class['class_name'] . '(' . $per_class['class_id'] . ')';

            if ($activeSheetIndex !== 0) {
                $objPHPExcel->createSheet();
            }
            $activeSheet = $objPHPExcel->setActiveSheetIndex($activeSheetIndex);

            // 创建班级sheet的科目表头
            foreach ($disciplines as $k => $v) {
                $cellCode = self::digitalToAlphabet($k) . '1';
                $activeSheet->setCellValue($cellCode, $v);
            }

            // 添加学生列到班级sheet中
            foreach ($current_class_student_names as $k => $v) {
                $cellCode = 'A' . ($k + 2);
                $activeSheet->setCellValue($cellCode, $v);
            }

            $objPHPExcel->getActiveSheet()->setTitle($sheetTitle);
            $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(self::ROW_HEIGHT);
            $objPHPExcel->getActiveSheet()->getColumnDimension()->setWidth(self::COLUMN_WIDTH);

            ++ $activeSheetIndex;
        }

        $objPHPExcel->setActiveSheetIndex(0);
        $fileName = "年级成绩模板-{$grade_full_name}";

        self::setHeaders($fileName);

        // output to a client’s web browser
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;

    }


    private static function digitalToAlphabet($digital)
    {
        $map = [
            0 => 'A',
            1 => 'B',
            2 => 'C',
            3 => 'D',
            4 => 'E',
            5 => 'F',
            6 => 'G',
            7 => 'H',
            8 => 'I',
            9 => 'J',
            10 => 'K',
            11 => 'L',
            12 => 'M',
            13 => 'N',
            14 => 'O',
            15 => 'P',
            16 => 'Q',
            17 => 'R',
            18 => 'S',
            19 => 'T',
            20 => 'U',
            21 => 'V',
            22 => 'W',
            23 => 'X',
            24 => 'Y',
            25 => 'Z',
        ];
        return $map[$digital];
    }

    private static function getPHPExcelObj($type)
    {
        // Create new PHPExcel object
        $objPHPExcel = new \PHPExcel();

        // Set document properties
        switch ($type) {
            case 'class':
                $objPHPExcel->getProperties()
                    ->setCreator("校园WEB端")
                    ->setLastModifiedBy("")
                    ->setTitle("班级考试成绩表")
                    ->setSubject("班级考试成绩模板")
                    ->setDescription("表头是科目，每一行是一名学生的所有成绩.")
                    ->setKeywords("班级 成绩 考试")
                    ->setCategory("成绩");
                break;
            case 'grade':
                $objPHPExcel->getProperties()
                    ->setCreator("校园平台教师端")
                    ->setLastModifiedBy("")
                    ->setTitle("年级考试成绩表")
                    ->setSubject("年级考试成绩模板")
                    ->setDescription("以班级为单位,分为多个sheet; 每个sheet内,表头是科目，每一行是一名学生的所有成绩.")
                    ->setKeywords("年级 成绩 考试")
                    ->setCategory("成绩");
                break;
            case 'study':
                $objPHPExcel->getProperties()
                    ->setCreator("校园WEB端")
                    ->setLastModifiedBy("")
                    ->setTitle("课程学习情况记录表")
                    ->setSubject("课程学习情况记录模板")
                    ->setDescription("表头是项目，每一行是一名学生的所有成绩.")
                    ->setKeywords("课程 学习 记录")
                    ->setCategory("学习记录");
                break;
            case 'experimental':
                $objPHPExcel->getProperties()
                    ->setCreator("校园WEB端")
                    ->setLastModifiedBy("")
                    ->setTitle("实验操作评价表")
                    ->setSubject("实验操作评价模板")
                    ->setDescription("表头是项目，每一行是一名学生的所有成绩.")
                    ->setKeywords("实验 操作 记录")
                    ->setCategory("学习记录");
                break;
            case 'course_score':
                $objPHPExcel->getProperties()
                    ->setCreator("校园WEB端")
                    ->setLastModifiedBy("")
                    ->setTitle("学业成绩及学科课程学习情况汇总表")
                    ->setSubject("学业成绩及学科课程学习情况汇总表模板")
                    ->setDescription("表头是项目，每一行是一名学生的所有成绩.")
                    ->setKeywords("学业成绩 学科课程学习 汇总")
                    ->setCategory("学习记录");
                break;
            case 'all-evaluate':
                $objPHPExcel->getProperties()
                    ->setCreator("校园WEB端")
                    ->setLastModifiedBy("")
                    ->setTitle("学生综合素养评定汇总表")
                    ->setSubject("学生综合素养评定汇总表模板")
                    ->setDescription("表头是项目，每一行是一名学生的所有成绩.")
                    ->setKeywords("学生 综合素养 评定汇总")
                    ->setCategory("学习记录");
                break;
            case 'vaccination':
                $objPHPExcel->getProperties()
                    ->setCreator("校园WEB端")
                    ->setLastModifiedBy("")
                    ->setTitle("幼儿疫苗接种记录表")
                    ->setSubject("幼儿疫苗接种记录表模板")
                    ->setDescription("表头是项目，每一行是一名学生的所有记录.")
                    ->setKeywords("幼儿 疫苗 记录")
                    ->setCategory("学生健康");
                break;

            case 'physical_check':
                $objPHPExcel->getProperties()
                    ->setCreator("校园WEB端")
                    ->setLastModifiedBy("")
                    ->setTitle("学生体质检查记录表")
                    ->setSubject("学生体质检查记录表模板")
                    ->setDescription("表头是项目，每一行是一名学生的所有记录.")
                    ->setKeywords("学生 体检 记录")
                    ->setCategory("学生健康");
                break;

            default :
                throw new \Exception('表格类型错误');
        }
        return $objPHPExcel;
    }

    private static function setHeaders($fileName)
    {
        // Redirect output to a client’s web browser (Excel5)
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename=' . $fileName . '.xls');
//        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=300');

        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: ' . gmdate('D, d M Y H:i:s', time() + 300) . ' GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0

    }


    public static function createClassStudentTemplate(){

        $activeSheetIndex = 0;
        $sheetTitle = '请勿修改表头';

        // Create new PHPExcel object
        $objPHPExcel = new \PHPExcel();

        $activeSheet = $objPHPExcel->setActiveSheetIndex($activeSheetIndex);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:I1');
        $activeSheet->setCellValue('A1','注意：请按照此表头顺序填相应的数据，请勿修改表头');
        $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray(
            array(
                'font' => array ('bold' => true ),
                //'alignment' => array( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
            ));

        $activeSheet->setCellValue('A2','卡号（必填）');
        $activeSheet->setCellValue('B2','姓名（必填）');
        $activeSheet->setCellValue('C2','家长姓名1（必填）');
        $activeSheet->setCellValue('D2','家长电话1（必填）');
        $activeSheet->setCellValue('E2','家长姓名2（选填）');
        $activeSheet->setCellValue('F2','家长电话2（选填）');
        $activeSheet->setCellValue('G2','学号（选填）');
        $activeSheet->setCellValue('H2','上学方式（走读生填1，住宿生填4）');
        $activeSheet->setCellValue('I2','性别（选填：男，女）');


        //Rename worksheet
        $objPHPExcel->getActiveSheet()->setTitle($sheetTitle);
        // set row height
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(self::ROW_HEIGHT);
        $objPHPExcel->getActiveSheet()->getColumnDimension()->setWidth(self::COLUMN_WIDTH);

        $fileName = "班级学生导入模板";
        //输出内容
        // set Http headers
        self::setHeaders($fileName);
        // output to a client’s web browser
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    public static function exportClassStudentData($class_full_name,$students){
        $activeSheetIndex = 0;
        $sheetTitle = $class_full_name;

        // Create new PHPExcel object
        $objPHPExcel = new \PHPExcel();

        $activeSheet = $objPHPExcel->setActiveSheetIndex($activeSheetIndex);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:J1');
        $activeSheet->setCellValue('A1',$class_full_name);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray(
            array(
                'font' => array ('bold' => true ),
                //'alignment' => array( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
            ));

        $activeSheet->setCellValue('A2','卡号');
        $activeSheet->setCellValue('B2','姓名');
        $activeSheet->setCellValue('C2','性别');
        $activeSheet->setCellValue('D2','学号');
        $activeSheet->setCellValue('E2','上学方式');
        $activeSheet->setCellValue('F2','民族');
        $activeSheet->setCellValue('G2','家长1姓名');
        $activeSheet->setCellValue('H2','家长1电话');
        $activeSheet->setCellValue('I2','是否绑定');
        $activeSheet->setCellValue('J2','家长2姓名');
        $activeSheet->setCellValue('K2','家长2电话');
        $activeSheet->setCellValue('L2','是否绑定');
        $activeSheet->setCellValue('M2','服务到期时间');

        //Rename worksheet
        $objPHPExcel->getActiveSheet()->setTitle($sheetTitle);
        // set row height
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(self::ROW_HEIGHT);
       // $objPHPExcel->getActiveSheet()->getColumnDimension()->setWidth(self::COLUMN_WIDTH);
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(18);
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(18);

        $line =3;
        foreach ($students as $k=>$v){//学生
            $activeSheet->setCellValue('A'.($line),$v['card_number']);
            $activeSheet->setCellValue('B'.($line),$v['name']);
            $activeSheet->setCellValue('C'.($line),$v['gender']);
            $activeSheet->setCellValue('D'.($line),$v['student_number']);
            $activeSheet->setCellValue('E'.($line),$v['way_to_school']);
            $activeSheet->setCellValue('F'.($line),$v['nation_id']);
            $first_guardian = current($v['guardians']);
            if($first_guardian){
                $activeSheet->setCellValue('G'.($line),$first_guardian['name']);
                $activeSheet->setCellValue('H'.($line),$first_guardian['mobile']);
                $activeSheet->setCellValue('I'.($line),$first_guardian['is_bind']?'是':'否');
            }

            if(count($v['guardians'])>1){
                $second_guardian = next($v['guardians']);
                $activeSheet->setCellValue('J'.($line),$second_guardian['name']);
                $activeSheet->setCellValue('K'.($line),$second_guardian['mobile']);
                $activeSheet->setCellValue('L'.($line),$second_guardian['is_bind']?'是':'否');
            }
            $activeSheet->setCellValue('M'.($line),$v['service_expire_at']);
            $line++;
        }
        $fileName = $class_full_name . "学生信息表";
        //输出内容
        // set Http headers
        self::setHeaders($fileName);
        // output to a client’s web browser
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }


    public  static  function  createCardTemplate(){
        $activeSheetIndex = 0;
        $sheetTitle = '卡号批量导入';
        // Create new PHPExcel object
        $objPHPExcel = new \PHPExcel();

        $activeSheet = $objPHPExcel->setActiveSheetIndex($activeSheetIndex);
        $activeSheet->setCellValue('A1','卡号（必填）');
        //Rename worksheet
        $objPHPExcel->getActiveSheet()->setTitle($sheetTitle);
        // set row height
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(self::ROW_HEIGHT);
        $objPHPExcel->getActiveSheet()->getColumnDimension()->setWidth(self::COLUMN_WIDTH);

        $fileName = "卡号批量导入模板";
        //输出内容
        // set Http headers
        self::setHeaders($fileName);
        // output to a client’s web browser
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }


}